package persistent.prestige.console.sqlparse;


import java.util.List;

import com.alibaba.druid.sql.ast.SQLStatement;
import com.alibaba.druid.sql.ast.statement.SQLSelectStatement;
import com.alibaba.druid.sql.dialect.mysql.parser.MySqlStatementParser;
import com.alibaba.druid.sql.dialect.mysql.visitor.MySqlSchemaStatVisitor;

public class SQLParseDemo {

	public static void main(String[] args) {
		
		//testParseInsertSql();
		//testMultimentInsert();
		//testMultimentInsert3();
		
		testLeftJoinWhere();
		
//		testCall();
		
//		testGroupBy();
		
//		testSQLSubqueryTableSouce();
		
		
//		testInsert();
		
//		testUpdate();
		
//		testDel();
		
	}
	
	public static void testDel() {
		String sql = "delete from acc_user a where a.id=1";
		testParseDel(sql);
	}
	
	public static void testUpdate() {
		String sql = "update acc_user a set a.id=1,a.name='dw' where a.email='1@163.com'";
		testParseUpdate(sql);
	}
	
	private static void testParseUpdate(String sql) {
		MySqlStatementParser parser = new MySqlStatementParser(sql); 
		
		SQLStatement stmt = parser.parseUpdateStatement();
		System.out.println(stmt);
	}
	
	private static void testParseDel(String sql) {
		MySqlStatementParser parser = new MySqlStatementParser(sql); 
		
		SQLStatement stmt = parser.parseDeleteStatement();
		System.out.println(stmt);
	}
	
	public static void testInsert() {
		String sql = "insert into es_user(id,name) values (1,2),(3,4);";
		testParse(sql);
	}
	
	public static void testSQLSubqueryTableSouce() {
		String sql = "select * from (select a.id,a.name from acc_user a) tmp";
		testParse(sql);
	}
	
	public static void testGroupBy() {
		String sql = "select id,name,count(*) from acc_user a group by a.id,a.name having count(*) > 1";
		testParse(sql);
	}
	
	
	private static void testParse(String sql) {
		MySqlStatementParser parser = new MySqlStatementParser(sql); 
		
		SQLStatement stmt = parser.parseStatement();
		
		System.out.println(stmt);
		
		
		MySqlSchemaStatVisitor visitor = new MySqlSchemaStatVisitor();
		stmt.accept(visitor);
		
		System.out.println(visitor.getConditions());
	}
	
	public static void testCall() {
		String sql = "call test_proc(@id,@goodsId)";
		
		MySqlStatementParser parser = new MySqlStatementParser(sql);  
		
		SQLStatement stmt = parser.parseStatement();
		
		System.out.println(stmt);
		
		
	}
	
	
	
	
	public static void testLeftJoinWhere() {
		String sql = "select " + 
						"a.id as orderId, " + 
						"a.order_no as orderNo,"+ 
						"b.goods_id as goodsId, " +
						"b.sku_id as skuId " + 
					"from es_order a inner join es_order_item b on b.order_id=a.id  " + 
					 " left join es_goods c on c.id = b.goods_id " +
					"where a.creator=15 or a.creator=17 and exists (select 1 from es_user u where u.id=a.creator and u.type='1')";
		
		
		testParse(sql);
		
	}
	
	
	
	public static void testMultimentInsert() {
		String sql = "insert INTO es_order_item(        order_id,        sku_id,        goods_id,        price,        num,        total_price,        creator     ) VALUES (        ?,        ?        ?,        ?,        ?,        ?,        ?     )      ;       insert INTO es_order_item(        order_id,        sku_id,        goods_id,        price,        num,        total_price,        creator     ) VALUES (        ?,        ?        ?,        ?,        ?,        ?,        ?     )" ; 
		
		MySqlStatementParser parser = new MySqlStatementParser(sql);  
		
		List<SQLStatement> stmts = parser.parseStatementList();
		
		if(stmts == null || stmts.isEmpty()) {
			System.out.println("没有找到合适的SQL语法对象");
		} else {
			for(SQLStatement stmt : stmts ) {
				System.out.println(stmt);
			}
		}
		
	}
	
	
	public static void testMultimentInsert3() {
		String sql = "INSERT INTO `es_community_sku` (`id`, `sku_id`, `community_id`, `goods_id`, `seller_id`) " +
				 "VALUES (21161, 1522, 19, 1388, 29),(21161, 1522, 19, 1388, 29),(21161, 1522, 19, 1388, 29)";
		
		MySqlStatementParser parser = new MySqlStatementParser(sql);  
		
		List<SQLStatement> stmts = parser.parseStatementList();
		
		if(stmts == null || stmts.isEmpty()) {
			System.out.println("没有找到合适的SQL语法对象");
		} else {
			for(SQLStatement stmt : stmts ) {
				System.out.println(stmt);
			}
		}
		
	}
	
	
	public static void testParseInsertSql(){
		
		
		// TODO Auto-generated method stub
				// /*!mycat:scheam=TEST1*/
		String sql = "select a.id,a.name,a.remark,b.id,b.user_name as username " +
				             "from acc_user a " +
				             "inner join acc_account b on a.id = b.user_id and a.email='d123@163.com' " +
				              "inner join acc_test c on c.id = b.user_id " +
				             "where a.name like '?' and exists ( select 1 from acc_d d where d.user_id=b.user_id )" ;
//				             " union " +
//				             "select d.id,d.name,d.remark,d.id,d.user_name as username from acc_td d";
				
	    try{
	        MySqlStatementParser parser = new MySqlStatementParser(sql);    
	        
	        SQLStatement s = parser.parseSelect();
	        
	        System.out.println(s);
	        
	        
	        SQLStatement statement = parser.parseStatement();
	        
	      //  List<SQLStatement> d = parser.parseStatementList();
	        
	        SQLSelectStatement st = (SQLSelectStatement) statement;
	        
	        MySqlSchemaStatVisitor visitor = new MySqlSchemaStatVisitor();
	        
	        st.accept(visitor);
	        
	        System.out.println(visitor.getCurrentTable());

	        System.err.println(visitor.getAliasMap());
	        
	        System.out.println(visitor.getTables());
	        
	        
	        
	        
	        
	        
	        
	        
	        
	        
	    //    System.out.println(statement);
	        
	        System.out.println("end");
	        
	       
	    }catch(Exception e){ // 发生异常，则返回原始 sql
	    	e.printStackTrace();
	       
	    }
	}

}
